package com.telecom.cms.dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.dto.IdaBooking;
import com.telecom.cms.util.Constant;

public class IdaCustomDAO 
{
	private static String db = Constant.getStPara("DATABASE");
	
	public static List findAllBooking(){
		List result = new ArrayList();
		Dao dao = new DaoImp();
		String sql = "SELECT * FROM " + db + ".IDA_BOOKING_REQUIREMENT ORDER BY Booking_Time DESC";
		try{
			ResultSet rs = dao.select(sql);
			while (rs.next()){
				IdaBooking booking = new IdaBooking();
				booking.setBookingId(rs.getInt("Booking_Id"));
				booking.setBookingName(rs.getString("Booking_Name"));
				booking.setRequirement(rs.getString("Requirement"));
				booking.setPurpose(rs.getString("Purpose"));
				booking.setTimeLimit(rs.getString("Time_Limit"));
				booking.setProcessResult(rs.getString("Process_Result"));
				booking.setBookingUser(rs.getString("Booking_User"));
				booking.setBookingTime(rs.getString("Booking_Time"));
				booking.setProcessUser(rs.getString("Process_User"));
				booking.setProcessTime(rs.getString("Process_Time"));
				booking.setStatus(rs.getString("Status"));
				result.add(booking);
			}
			rs.close();
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return result;
	}
	
	public static IdaBooking findBooking(int bid){
		Dao dao = new DaoImp();
		String sql = "SELECT * FROM " + db + ".IDA_BOOKING_REQUIREMENT WHERE Booking_Id=" + bid;
		try{
			IdaBooking booking = new IdaBooking();
			ResultSet rs = dao.select(sql);
			if (rs.next()){
				booking.setBookingId(rs.getInt("Booking_Id"));
				booking.setBookingName(rs.getString("Booking_Name"));
				booking.setRequirement(rs.getString("Requirement"));
				booking.setPurpose(rs.getString("Purpose"));
				booking.setTimeLimit(rs.getString("Time_Limit"));
				booking.setProcessResult(rs.getString("Process_Result"));
				booking.setBookingUser(rs.getString("Booking_User"));
				booking.setBookingTime(rs.getString("Booking_Time"));
				booking.setProcessUser(rs.getString("Process_User"));
				booking.setProcessTime(rs.getString("Process_Time"));
				booking.setStatus(rs.getString("Status"));
			}
			rs.close();
			return booking;
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
		return null;
	}
	
	public static void insertBooking(IdaBooking booking){
		Dao dao = new DaoImp();
		String sql = "INSERT INTO " + db + ".IDA_BOOKING_REQUIREMENT(Booking_Name,Requirement,Purpose,Time_Limit," +
				"Process_Result,Booking_User,Booking_Time,Process_User,Process_Time,Status) " +
				"VALUES('" + booking.getBookingName() + "','" +
					booking.getRequirement() + "','" +
					booking.getPurpose() + "','" +
					booking.getTimeLimit() + "','" +
					booking.getProcessResult() + "','" +
					booking.getBookingUser() + "','" +
					booking.getBookingTime() + "','" +
					booking.getProcessUser() + "'," +
					(booking.getProcessTime().equals("") ? "" : "'" + booking.getProcessTime() + "'") + ",'" +
					booking.getStatus() + "')";
		try{
			dao.insert(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public static void updateBooking(IdaBooking booking){
		Dao dao = new DaoImp();
		String sql = "UPDATE " + db + ".IDA_BOOKING_REQUIREMENT SET " +
				"Booking_Name='" + booking.getBookingName() + "'," +
				"Requirement='" + booking.getRequirement() + "'," +
				"Purpose='" + booking.getPurpose() + "'," +
				"Time_Limit='" + booking.getTimeLimit() + "'," +
				"Process_Result='" + booking.getProcessResult() + "'," +
				"Process_User='" + booking.getProcessUser() + "'," +
				(booking.getProcessTime().equals("") ? "" : "Process_Time='" + booking.getProcessTime() + "',") + 
				"Status='" + booking.getStatus() + "' " +
			"WHERE Booking_Id=" + booking.getBookingId();
		try{
			dao.update(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
	
	public static void deleteBooking(IdaBooking booking){
		Dao dao = new DaoImp();
		String sql = "DELETE FROM " + db + ".IDA_BOOKING_REQUIREMENT WHERE Booking_Id=" + booking.getBookingId();
		try{
			dao.delete(sql);
		}
		catch (Exception e){
			e.printStackTrace();
		}
		finally{
			dao.closeconn();
		}
	}
}
